/*
This code creates Table 1: County Characteristics
*/

log using "$logs\Table1.txt", replace text name(table1)

use "$data_deri\tbl1_ratedata_17", clear
merge 1:1 stcty using "$data_deri\tbl1_enroll_11", gen(_merge1)
merge 1:1 stcty using "$data_deri\tbl1_enroll_19", gen(_merge2)
merge 1:1 stcty using "$data_deri\tbl1_pay_11", gen(_merge3)
merge 1:1 stcty using "$data_deri\tbl1_pay_19", gen(_merge4)
merge m:1 statecode using "$data_deri\region_xwalk", gen(_merge5)

destring bnchmrk*, replace ignore(",")
destring eligibles*, replace ignore(",")
destring enrolled*, replace ignore("," "*")
destring penetration*, replace ignore("%") percent

label variable bnchmrk17_aca "post-ACA benchmark in 2017"
label variable bnchmrk17_preaca "pre-ACA benchmark in 2017"
label variable enrolled11 "MA enrollee count in 2011"
label variable enrolled19 "MA enrollee count in 2019"
label variable penetration11 "MA Penetration in 2011"
label variable penetration19 "MA Penetration in 2019"
label variable tot_pay_ra_11 "Average MA PM/PM Payment in 2011"
label variable tot_pay_ra_19 "Average MA PM/PM Payment in 2019"


local total_counties = _N
count if q4_prop_flag_rev==0
local large_cut_counties = r(N)
count if q4_prop_flag_rev==1
local small_cut_counties = r(N)

table1_mc [fweight=eligibles11], vars(bnchmrk17_aca contn %9.2f \ ///
				bnchmrk17_preaca contn %9.2f \ ///
				enrolled11 contn %9.0fc \ ///
				enrolled19 contn %9.0fc \ ///
				penetration11 contn %9.2f \ ///
				penetration19 contn %9.2f \ ///
				tot_pay_ra_11 contn %9.2f \ ///
				tot_pay_ra_19 contn %9.2f \ ///
				region cat %3.1f) ///
				by(q4_prop_flag_rev) total(before) onecol percent clear

keep factor q4_prop_flag_rev_T q4_prop_flag_rev_0 q4_prop_flag_rev_1
export excel using "$output\Table1.xlsx", sheetreplace

* change group counts to reflect number of counties instead of number on beneficiaries
putexcel set "$output\Table1.xlsx", modify
putexcel B2="(n=`total_counties')" C2="(n=`large_cut_counties')" D2="(n=`small_cut_counties')"

log close table1
